<?php

require_once(dirname(__FILE__).'/Base.php');

class DB_Note extends DB_Base {
    public function insert ($member_id, $title='', $date='0', $time=null, $progress=0, $status=0) {
        $sth = $this->dbh->prepare('
            INSERT INTO
                schedules
            SET
                type         = 2,
                member_id    = ?,
                title        = ?,
                start_date   = ?,
                start_time   = ?,
                progress     = ?,
                public_level = ?
        ');
        if (!$sth) throw new Exception($this->dbh->error);
        $sth->bind_param('dsssdd', $member_id, $title, $date, $time, $progress, $status);
        $sth->execute();
        return $sth->insert_id;
    }

    public function update ($id, $title = '', $date='0', $time=null, $progress = 0, $status = 0) {
        $sth = $this->dbh->prepare('
            UPDATE
                schedules
            SET
                title        = ?,
                start_date   = ?,
                start_time   = ?,
                progress     = ?,
                public_level = ?
            WHERE
                id = ?
        ');
        $sth->bind_param('sssddd', $title, $date, $time, $progress, $status, $id);
        $sth->execute();
        return $sth->affected_rows;
    }

    public function delete ($id) {
        $sth = $this->dbh->prepare('
            DELETE FROM
                schedules
            WHERE
                id = ?
        ');
        $sth->bind_param('d', $id);
        $sth->execute();
        return $sth->affected_rows;
    }

    public function select_by_id ($id) {
        $sth = $this->dbh->prepare('
            SELECT
                member_id,
                title,
                start_date,
                start_time,
                progress,
                public_level
            FROM
                schedules
            WHERE
                id = ?
        ');
        $sth->bind_param('d', $id);
        $sth->execute();
        $sth->bind_result($member_id, $title, $date, $time, $progress, $status);
        if ($sth->fetch()) {
            return array(
                'member_id' => $member_id,
                'title'     => $title,
                'date'      => $date,
                'time'      => $time,
                'progress'  => $progress,
                'status'    => $status,
            );
        }
        return array();
    }

    public function select_by_member ($member_id_list) {
        if (!is_array($member_id_list)) {
            $member_id_list = array($member_id_list);
        }
        foreach ($member_id_list as &$member_id) {
            is_numeric($member_id) or die;
            $member_id = "'$member_id'";
        }

        $place_holder = implode(', ', $member_id_list);

        $sth = $this->dbh->prepare("
            SELECT
                id,
                member_id,
                title,
                start_date,
                start_time,
                progress,
                public_level
            FROM
                schedules
            WHERE
                member_id IN ($place_holder) AND
                type = 2
        ");
        $sth->execute();
        $sth->bind_result($id, $member_id, $title, $date, $time, $progress, $status);

        $result = array();
        while ($sth->fetch()) {
            $result[] = array(
                'id'       => $id,
                'member_id' => $member_id,
                'title'    => $title,
                'date'     => $date,
                'time'     => $time,
                'progress' => $progress,
                'status'   => $status,
            );
        }
        return $result;
    }

    public function select_by_member_date ($member_id_list, $from, $to) {
        if (!is_array($member_id_list)) {
            $member_id_list = array($member_id_list);
        }
        foreach ($member_id_list as &$member_id) {
            is_numeric($member_id) or die;
            $member_id = "'$member_id'";
        }

        $place_holder = implode(', ', $member_id_list);

        $sth = $this->dbh->prepare("
            SELECT
                id,
                member_id,
                title,
                start_date,
                start_time,
                progress,
                public_level
            FROM
                schedules
            WHERE
                member_id IN ($place_holder) AND
                start_date BETWEEN ? AND ? AND
                type = 2
        ");
        $sth->bind_param('ss', $from, $to);
        $sth->execute();
        $sth->bind_result($id, $member_id, $title, $date, $time, $progress, $status);

        $result = array();
        while ($sth->fetch()) {
            $result[] = array(
                'id'       => $id,
                'member_id' => $member_id,
                'title'    => $title,
                'date'     => $date,
                'time'     => $time,
                'progress' => $progress,
                'status'   => $status,
            );
        }
        return $result;
    }

    public function select_title_count_by_member ($member_id) {
        $sth = $this->dbh->prepare('
            SELECT
                title, count(*) AS count
            FROM
                schedules
            WHERE
                member_id = ? AND
                type = 2
            GROUP BY title
        ');
        $sth->bind_param('d', $member_id);
        $sth->execute();
        $sth->bind_result($title, $count);

        $result = array();
        while ($sth->fetch()) {
            $result[$title] = $count;
        }
        return $result;
    }
    
    public function select_recent_days ($member_id_list, $start_date, $offset = 0, $limit = 3) {
        if (!is_array($member_id_list)) {
            $member_id_list = array($member_id_list);
        }
        foreach ($member_id_list as &$member_id) {
            is_numeric($member_id) or die;
            $member_id = "'$member_id'";
        }

        $member_id_in = implode(', ', $member_id_list);

        $sth = $this->dbh->prepare("
            SELECT
                start_date
            FROM
                schedules
            WHERE
                member_id IN ($member_id_in) AND
                start_date >= ?
            GROUP BY
                start_date
            ORDER BY start_date
            LIMIT ?, ?
        ");
        $sth->bind_param('sdd', $start_date, $offset, $limit);
        $sth->execute();
        $sth->bind_result($start_date);
        
        $result = array();
        while ($sth->fetch()) {
            $result[] = $start_date;
        }
        return $result;
    }
}
